package db.queries;

import java.sql.SQLException;
import java.util.List;

import core.exceptions.DBAccessException;
import core.objects.Book;
import core.objects.BookLoan;
import core.objects.ObjectWithID;
import core.objects.User;
import core.objects.enums.LoanStatusEnum;
import db.DBHandler;
import db.dbTables.AbstractDBTable;
import db.dbTables.BookTable;
import db.dbTables.DbColumn;
import db.dbTables.LoanTable;
import db.dbTables.UserTable;
import db.queries.core.ObjectQueries;
import db.queries.core.QueriesEnvironment;

public class LoanQueries extends ObjectQueries<BookLoan> {

    private static final int QUERY_TYPE_REGULAR = 0;
    private static final int QUERY_TYPE_BOOK = 1;
    private static final int QUERY_TYPE_LOANED = 2;

    private int queryType = QUERY_TYPE_REGULAR;

    @Override
    protected AbstractDBTable getTable() {
        return QueriesEnvironment.getLoanTable();
    }
        
    @SuppressWarnings("unchecked")
	@Override
    public List<BookLoan> select(int id, ObjectWithID obj)
    		throws DBAccessException {
    	if (obj instanceof User) {
    		try {
    			return (List<BookLoan>) DBHandler.convertResultSetToBaseObjectList(
    					DBHandler.executeQuery(
    							QueriesEnvironment.selectQuery(
    									QueriesEnvironment.getLoanTable().bookId.getName() + " = ? AND "
    									+ QueriesEnvironment.getLoanTable().loanerId.getName() + " = ? "
    									+ "ORDER BY " + QueriesEnvironment.getLoanTable().startDate.getName() + " DESC"
    									+ " LIMIT 1",
    									new Object[] { id, obj.getID() },
    									QueriesEnvironment.getLoanTable().getDbColumns().toArray(new DbColumn[0]))),
    						QueriesEnvironment.getLoanTable());		
    		} catch (SQLException e) {
    			throw handleSQLException(e);
    		}
    		
    		
    	} else {
    		return super.select(id, obj);
    	}
    }
    
    @SuppressWarnings("unchecked")
	@Override
    public List<BookLoan> select(int id) throws DBAccessException {
    	try {
    		return (List<BookLoan>)DBHandler.convertResultSetToBaseObjectList(
    				DBHandler.executeQuery(
    						QueriesEnvironment.selectQuery(
    								"(" + QueriesEnvironment.getLoanTable().loanerId.getName() + " = ? OR "
    								+ QueriesEnvironment.getLoanTable().loanedId.getName() + " = ? ) "
    								+ " AND " + QueriesEnvironment.getLoanTable().loanStatus + " = ? "
    								+ " ORDER BY " + QueriesEnvironment.getLoanTable().startDate + " DESC ;",
    								new Object[] { id, id, LoanStatusEnum.Ongoing.toString() },
    								QueriesEnvironment.getLoanTable().getDbColumns().toArray(new DbColumn[0]))),
    				QueriesEnvironment.getLoanTable());
    	} catch (SQLException e) {
    		throw handleSQLException(e);
    	}
    }
    
    @Override
    protected DBAccessException handleSQLException(SQLException e) {
    	if (e.getMessage() != null) {
    		String message = e.getMessage();
    		if (message.endsWith("cannot be null")) {
    			if (message.contains(LoanTable.COLUMN_LOAN_STATUS)) {
    				return new DBAccessException("Error: missing loan status", e);
    			} else if (message.contains(LoanTable.COLUMN_START_DATE)) {
    				return new DBAccessException("Error: missing loan start date", e);
    			}
    		}
    	}
    	return super.handleSQLException(e);
    }
    
	@Override
	protected DBAccessException foreignKeyConstrainstViolation(String key,
			Throwable e) {
		if (key.equals(BookTable.COLUMN_ID)) {
			return new DBAccessException("Error: unknown book", e);
		} else if (key.equals(UserTable.COLUMN_ID)) {
			return new DBAccessException("Error: unknown user", e);
		} else if (key.equals(LoanTable.COLUMN_LOANER_ID)) {
			return new DBAccessException("Error: unknown loaner - user does not exist", e);			
		} else if (key.equals(LoanTable.COLUMN_LOANED_ID)) {
			return new DBAccessException("Error: cannot loan to this person - user does not exist", e);
		} else {
			return new DBAccessException("Unknown error has occured", e);
		}
	}

	@Override
	protected void determineField(Object obj) {
		if (obj instanceof Book) {
			queryType = QUERY_TYPE_BOOK;
		} else if (obj instanceof User) {
			queryType = QUERY_TYPE_LOANED;
		} else {
			queryType = QUERY_TYPE_REGULAR;
		}
	}

    @Override
    protected DbColumn getIDColumn() {
        switch (queryType) {
            case QUERY_TYPE_BOOK:
                return QueriesEnvironment.getLoanTable().bookId;
            case QUERY_TYPE_LOANED:
                return QueriesEnvironment.getLoanTable().loanedId;
            default:
                return QueriesEnvironment.getLoanTable().loanerId;
        }
    }

	@Override
	protected DbColumn getActualIDColumn() {
		return QueriesEnvironment.getLoanTable().bookId;
	}
}
